package com.gaattc.dao;

import com.gaattc.model.Goods;
import com.gaattc.model.GoodsSpecs;
import com.gaattc.model.User;
import com.gaattc.model.bo.AddSpecsBO;
import com.gaattc.model.bo.AddTypeBO;
import com.gaattc.model.bo.AskGoodsMsgBO;
import com.gaattc.model.vo.*;
import com.gaattc.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.math.BigInteger;
import java.sql.SQLException;
import java.util.List;

public class GoodsDaoImpl implements GoodsDao {
    @Override
    public List<GoodsTypeVO> getType() {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<GoodsTypeVO> list = null;
        try {
            list = queryRunner.query("select * from goods_type", new BeanListHandler<>(GoodsTypeVO.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List<GetGoodsByTypeVO> getGoodsByType(int typeId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<GetGoodsByTypeVO> list = null;
        try {
            list = queryRunner.query("select g.id,g.img,g.name,g.typeId,s.unitPrice as price,s.stockNum \n" +
                            "from goods as g join goods_specs as s on g.id=s.goodsId \n" +
                            "where g.typeId = ? and s.unitPrice = (select min(s1.unitPrice) from goods_specs as s1 where s1.goodsId = g.id);",
                    new BeanListHandler<>(GetGoodsByTypeVO.class), typeId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List<GetGoodsByTypeVO> getAllGoodsByType() {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<GetGoodsByTypeVO> list = null;
        try {
            list = queryRunner.query("select g.id,g.img,g.name,g.typeId,s.unitPrice as price,s.stockNum \n" +
                            "from goods as g join goods_specs as s on g.id=s.goodsId \n" +
                            "where s.unitPrice = (select min(s1.unitPrice) from goods_specs as s1 where s1.goodsId = g.id);",
                    new BeanListHandler<>(GetGoodsByTypeVO.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int addType(AddTypeBO addTypeBO) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            return queryRunner.update("insert into goods_type (name) values (?)",
                    addTypeBO.getName());
        } catch (SQLException e) {
            e.printStackTrace();
            return 10000;
        }
    }

    @Override
    public int deleteType(int typeId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            queryRunner.update("delete from goods_type where id = ?", typeId);
            queryRunner.update("delete from goods where typeId = ?", typeId);
            return 1;
        } catch (SQLException e) {
            e.printStackTrace();
            return 10000;
        }
    }

    @Override
    public int deleteGoods(int id) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            queryRunner.update("delete from goods where id = ?", id);
            queryRunner.update("delete from goods_specs where goodsId = ?", id);
            return 1;
        } catch (SQLException e) {
            e.printStackTrace();
            return 10000;
        }
    }

    @Override
    public void addGoods(Goods goods) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            queryRunner.update("insert into goods values (?,?,?,?,?)",
                    goods.getId(),goods.getImg(),goods.getName(),goods.getTypeId(),goods.getDesc());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public int getLastId() {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        //BigInteger   count  Long
        BigInteger query = null;
        try {
            query = (BigInteger) runner.query("select last_insert_id()", new ScalarHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query != null ? query.intValue() : 0;
    }


    @Override
    public void addSpecs(List<AddSpecsBO> specList, int goodsId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        for (AddSpecsBO addSpecsBO : specList) {
            try {
                queryRunner.update("insert into goods_specs values (?,?,?,?,?)",
                        null,addSpecsBO.getSpecName(),addSpecsBO.getStockNum(),addSpecsBO.getUnitPrice(),goodsId);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void addSpecs(List<GoodsSpecs> specList) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        for (GoodsSpecs goodsSpecs : specList) {
            try {
                queryRunner.update("insert into goods_specs values (?,?,?,?,?)",
                        goodsSpecs.getId(),goodsSpecs.getSpecName(),goodsSpecs.getStockNum(),goodsSpecs.getUnitPrice(),goodsSpecs.getGoodsId());
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public Goods getGoodsInfo_Goods(int id) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        Goods goods = null;
        try {
            goods = queryRunner.query("select * from goods where id = ?", new BeanHandler<>(Goods.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goods;
    }

    @Override
    public List<GoodsSpecs> getGoodsInfo_Specs(int id) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<GoodsSpecs> list = null;
        try {
            list = queryRunner.query("select * from goods_specs where goodsId = ?",
                    new BeanListHandler<>(GoodsSpecs.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public void deleteSpec(GoodsSpecs goodsSpecs) {
        int goodsId = goodsSpecs.getGoodsId();
        String specName = goodsSpecs.getSpecName();
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            queryRunner.update("delete from goods_specs where goodsId = ? and specName = ?",
                    goodsId, specName);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public List<MsgVO> msg(int state) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<MsgVO> msg = null;
        try {
            msg = queryRunner.query("select * from msg where state = ?", new BeanListHandler<>(MsgVO.class), state);
            for (MsgVO m : msg) {
                String goodsName = (String) queryRunner.query("select name from goods where id = ?",
                        new ScalarHandler(), m.getGoodsId());
                String userName = (String) queryRunner.query("select nickname from user where id = ?",
                        new ScalarHandler(), m.getUserId());
                MsgGoodsVO goods = new MsgGoodsVO(goodsName);
                MsgUserVO user = new MsgUserVO(userName);
                m.setGoods(goods);
                m.setUser(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return msg;
    }

    @Override
    public void reply(MsgVO msgVO) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            queryRunner.update("update msg set replyContent = ? ,state = 0, replytime = now() where id = ?",
                    msgVO.getContent(), msgVO.getId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public List<GetGoodsMsgVO> getGoodsMsg(int id) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<GetGoodsMsgVO> list = null;
        try {
            list = queryRunner.query("select id,userId,content,createtime as time,replyContent,replytime from msg where goodsId = ?",
                    new BeanListHandler<>(GetGoodsMsgVO.class), id);
            for (GetGoodsMsgVO m : list) {
                String asker = getAsker(m.getUserId());
                m.setAsker(asker);
                ReplyVO replyVO = new ReplyVO(m.getReplyContent(), m.getReplytime());
                m.setReply(replyVO);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public String getAsker(int userId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String asker = null;
        try {
            asker = (String)queryRunner.query("select nickName from user where id = ?", new ScalarHandler(), userId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return asker;
    }

    @Override
    public UserGetGoodsInfoVO userGetGoodsInfo(int id) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        UserGetGoodsInfoVO info = null;
        try {
            info = queryRunner.query("select * from goods where id = ?", new BeanHandler<>(UserGetGoodsInfoVO.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return info;
    }

    @Override
    public List<CommentVO> getGoodsComment(int goodsId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<CommentVO> list = null;
        try {
            list = queryRunner.query("select score,id,specName,comment,time,userId,nickname from comment where goodsId = ?", new BeanListHandler<>(CommentVO.class),
                    goodsId);
            for (CommentVO c : list) {
                c.setUser(new User(c.getNickname()));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public Double getAvgScore(int goodsId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        Double avg = null;
        try {
            avg = (Double)queryRunner.query("select avg(score) from comment where goodsId = ?", new ScalarHandler(), goodsId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return avg;
    }

    @Override
    public List<GetGoodsByTypeVO> searchGoods(String keyword) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<GetGoodsByTypeVO> list = null;
        try {
            list = queryRunner.query("select g.id,g.img,g.name,g.typeId,s.unitPrice as price,s.stockNum \n" +
                            "from goods as g join goods_specs as s on g.id=s.goodsId \n" +
                            "where g.name like ? and s.unitPrice = (select min(s1.unitPrice) from goods_specs as s1 where s1.goodsId = g.id);",
                    new BeanListHandler<>(GetGoodsByTypeVO.class), "%" + keyword + "%");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public User getUser(String token) {
        User user = null;
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            user = runner.query("select * from user where email = ?",new BeanHandler<>(User.class), token);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    @Override
    public void askGoodsMsg(AskGoodsMsgBO askGoodsMsgBO, int userId) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            runner.update("insert into msg values(null, ?, ?, ?, ?, now(), null, null)",
                    userId, askGoodsMsgBO.getGoodsId(), askGoodsMsgBO.getMsg(), 1);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}
